-- Resets the SortOrder column on all records within a given Root Hierarchy so that each is a multiple
-- of 2048.  This allows for 12 worst case inserts before a resort is needed.
CREATE PROCEDURE [dbo].[asi_HierarchyResort]
    @rootHierarchyKey uniqueidentifier
AS
BEGIN
   SET NOCOUNT ON

   CREATE TABLE #Reorder (
      NewSortOrder int IDENTITY (0,2048),
      HierarchyKey uniqueidentifier)

   EXEC asi_HierarchyResortChildren @rootHierarchyKey

   UPDATE Hierarchy
      SET SortOrder = NewSortOrder
     FROM Hierarchy INNER JOIN #Reorder ON Hierarchy.HierarchyKey = #Reorder.HierarchyKey

   SET NOCOUNT OFF
END

GO